Working with CSV files
It can be hard finding a column when working with csv files (comma-separated values). It's a lot easier if you source the following script. You can then enter a command like :Csv 23 to highlight column 23. " Highlight a column in csv text. " :Csv 1 " highlight first column " :Csv 12 " highlight twelfth column " :Csv 0 " switch off highlight function! CSVH(colnr) if a:colnr > 1 let n = a:colnr - 1 execute 'match Keyword /^\(^,*,\)\{'.n.'}\zs^,*/' execute 'normal! 0'.n.'f,' elseif a:colnr 1 match Keyword /^^,*/ normal! 0 else match endif endfunction command! -nargs=1 Csv :call CSVH() Viewing csv fields The following commands can be entered to convert csv text to columns for easy viewing. Work on a temporary copy of your data because these commands will damage it! " Convert csv text to columns (press u to undo). " Warning: This deletes ',' and crops wide columns. :let width = 20 :let fill = repeat(' ', width) :%s/\(^,*\),\=/\=strpart(submatch(1).fill, 0, width)/ge :%s/\s\+$//ge Alternatively, you can change each comma to a newline to put each field on its own line: " Change CSV fields on current line to a list of separate items. :s/,/\r/g " Same, for all lines. :%s/,/\r/g In the replace text of a substitute, \r substitutes a newline. Navigating in csv text The simple script given above does not provide easy navigation, and it assumes that commas are only used as delimiters. The following version is a much better tool for editing CSV files. One can use the xls2csv Perl script to convert Excel files to CSV, and then view/edit with Vim. Features include: #Fields are correctly highlighted, according to the CSV specification: quotes, commas inside quotes, quote inside quotes are all correctly processed. #It does not go beyond the last column (from a count of the columns in the first three lines). #HJKL go left, down, up, right by "cell". Focus is set to the first character of the cell. Join is remapped to ;j. #Ctrl-f, Ctrl-b will stay in the same column. #0 and $ are changed to highlight the first and last cell. Focus is set to the first character of the cell. #The column number and heading (from the first line in the buffer) are displayed when moving around. Create file ~/.vim/ftplugin/csv.vim (Unix) or $HOME/vimfiles/ftplugin/csv.vim (Windows) containing the script below. Define file type detection for *.csv files (see [[Vim_setup_to_edit_MoinMoin_wiki_files#File type detection|file type detection for moin]]): autocmd BufNewFile,BufRead *.csv setf csv Open a file (named anything.csv) that contains fields separated by commas. Use H J K L 0 $ to move from cell to cell. Press ;j if you need to join two lines. This is the ftplugin csv.vim script: if v:version < 700 finish endif if exists('b:did_ftplugin') | finish | endif let b:did_ftplugin = 1 " get the number of columns. Count in first and last three lines in case -- at " least one of them should not be empty function! s:GetNumCols() let b:csv_max_col = 1 for l in 2, 3, line('$') - 2, line('$') - 1, line('$') " Determine number of columns by counting the (unescaped) commas. " Note: The regexp may also return unbalanced ", so filter out anything " which isn't a comma in the second pass. let c = strlen(substitute(substitute(getline(l), '\%(\%("\%(^"\|""\)*"\)\|\%(^,"*\)\)', , 'g'), '"', '', 'g')) + 1 if c > b:csv_max_col let b:csv_max_col = c endif endfor if b:csv_max_col <= 1 let b:csv_max_col = 10000 echohl WarningMsg echo "No comma-separated columns were detected. " echohl NONE endif return b:csv_max_col endfunction " Return the regex that can be used to find the n-th column function! s:GetExpr(colnr) if a:colnr > 1 return '^\%(\%(\%("\%(^"\|""\)*"\)\|\%(^,"*\)\),\)\{' . (a:colnr - 1) . '}\%(\%("\zs\%(^"\|""\)*\ze"\)\|\%(\zs^,"*\ze\)\)' else return '^\%(\%(\%("\zs\%(^"\|""\)*\ze"\)\|\%(\zs^,"*\ze\)\)\)' endif endfunction " Extract and echo the column header on the status line function! s:PrintColInfo(colnr) let l:colHeading = substitute( matchstr( getline(1), s:GetExpr(a:colnr) ), '^\s*\(.*\)\s*$', '\1', '' ) let l:info = 'Column ' . a:colnr if ! empty(l:colHeading) let l:info .= ': ' . l:colHeading endif " Limit length to avoid "Hit ENTER" prompt. echo strpart(l:info, 0, (&columns / 2)) . (len(l:info) > (&columns / 2) ? "..." : "") endfunction " Highlight n-th column function! s:Highlight(colnr) execute 'match Keyword /' . s:GetExpr(a:colnr) . '/' call s:Focus_Col(a:colnr) endfunction " Focus the cursor on the n-th column of the current line function! s:Focus_Col(colnr) normal! 0 call search(s:GetExpr(a:colnr), '', line('.')) call s:PrintColInfo(a:colnr) endfunction " Highlight next column function! s:HighlightNextCol() if b:csv_column < b:csv_max_col let b:csv_column += 1 endif call s:Highlight(b:csv_column) endfunction " Highlight previous column function! s:HighlightPrevCol() if b:csv_column > 1 let b:csv_column -= 1 endif call s:Highlight(b:csv_column) endfunction " Get the number of columns call s:GetNumCols() " Start by highlighting the first column. " But only if this buffer is brand new, not on reloading or resetting of " filetype. if ! exists('b:csv_column') let b:csv_column = 1 endif " Now automatically handled by :autocmd BufEnter. "silent call s:Highlight(b:csv_column) nnoremap H :call HighlightPrevCol() nnoremap L :call HighlightNextCol() nnoremap J :call Focus_Col(b:csv_column) nnoremap K :call Focus_Col(b:csv_column) nnoremap :call Focus_Col(b:csv_column) nnoremap :call Focus_Col(b:csv_column) nnoremap 0 :let b:csv_column=1:call Highlight(b:csv_column) nnoremap $ :let b:csv_column=b:csv_max_col:call Highlight(b:csv_column) nnoremap J J nnoremap K K setlocal nowrap " Undo the stuff we changed. let b:undo_ftplugin = 'setlocal wrap<|match none' " The :match is window-local, not buffer-local, so it can persist even when the " filetype is undone or the buffer changed. execute 'augroup csv' . bufnr() autocmd! " These events only highlight in the current window. " Note: Highlighting gets slightly confused if the same buffer is present in " two split windows next to each other, because then the events aren't fired. autocmd BufLeave match none autocmd BufEnter silent call Highlight(b:csv_column) augroup END Comments Wow! This tip is the best example of wiki power that I've seen. The original tip had a lot of promise, but also irritations. Two different (I think) anonymous users added and edited an amazing new script. I spent a lot of time tweaking the script and rewording the tip. Now Inkarkat has elevated it to the next level. Magnificent result! --JohnBeckett 01:16, 10 July 2008 (UTC)